package com.tony.server.controller;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.tony.easymvc.core.TableUtils;
import com.tony.easymvc.mapper.DbInfoMapper;
import com.tony.easymvc.mapper.ProjectBizMapper;
import com.tony.easymvc.mapper.ProjectInfoMapper;
import com.tony.easymvc.mapper.ProjectTablesMapper;
import com.tony.easymvc.po.DbInfo;
import com.tony.easymvc.po.HtmlOperation;
import com.tony.easymvc.po.ProjectBiz;
import com.tony.easymvc.po.ProjectInfo;
import com.tony.easymvc.po.ProjectTables;
import com.tony.easymvc.po.ProjectTablesExample;
import com.tony.server.JDBC;
import com.tony.server.service.ProjectBizService;
import com.tony.vo.MobileBaseBeanUtils;
import com.tony.vo.Result;
import com.tony.vo.ResultTableBean;

@Controller
@RequestMapping("table")
public class TableController {

	@Resource
	ProjectInfoMapper projectInfoMapper;
	@Resource
	DbInfoMapper dbInfoMapper;
	@Resource
	ProjectTablesMapper projectTablesMapper;
	@Resource
	ProjectBizMapper projectBizMapper;
	@Resource
	ProjectBizService projectBizServiceImpl;

	@RequestMapping("list")
	@ResponseBody
	public Result list(Integer bizId) {
		ProjectBiz projectBiz = projectBizServiceImpl.selectProjectBizFillDb(bizId);

		if (projectBiz == null || projectBiz.getMaster() == null) {
			return new Result(false, "业务id : " + bizId + "不存在", null);
		}
		List<String> tableNames = Lists.newArrayList();
		List<ResultTableBean> return_list = new ArrayList<ResultTableBean>();
		try {
			tableNames = projectBizServiceImpl.selectAllTables(projectBiz);
			ProjectTablesExample example = new ProjectTablesExample();
			example.or().andBizIdEqualTo(bizId);
			List<ProjectTables> db_return_list = projectTablesMapper.selectByExample(example);

			Map<String, ProjectTables> project_map = new HashMap<String, ProjectTables>();
			if (db_return_list != null && db_return_list.size() > 0) {
				for (ProjectTables projectTables : db_return_list) {
					project_map.put(projectTables.getTableName(), projectTables);
				}
			}
			for (String str : tableNames) {
				ResultTableBean item_bean = new ResultTableBean();
				item_bean.setTableName(str);
				if (project_map.containsKey(str)) {
					ProjectTables map_bean = project_map.get(str);
					MobileBaseBeanUtils.copyFields(map_bean, item_bean);
					item_bean=JSONObject.toJavaObject(map_bean.getOpe(), ResultTableBean.class);
					item_bean.setCheck(true);
				}
				return_list.add(item_bean);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return new Result(false, e.getMessage(), return_list);
		}
		return new Result(true, "", return_list);

	}

	@RequestMapping("getSavelist")
	@ResponseBody
	public Result getSavelist(Integer projectId) {
		// if(projectId==null){
		// return new Result(false, "项目编号无效!", null);
		// }
		// List<String> return_list=new ArrayList<String>();
		// ProjectTablesExample example=new ProjectTablesExample();
		// example.or().andProjectIdEqualTo(projectId);
		// List<ProjectTables>
		// db_return_list=projectTablesMapper.selectByExample(example);
		/*
		 * if(db_return_list!=null&&db_return_list.size()>0){ for (ProjectTables
		 * projectTables : db_return_list) {
		 * return_list.add(projectTables.getTableName()); } }
		 */
		/*
		 * ProjectInfo projectInfo = projectInfoMapper.selectByPrimaryKey(projectId); if
		 * (projectInfo == null || projectInfo.getMasterDb() == null) { return new
		 * Result(false, "项目id : " + projectId + "不存在", null); } DbInfo dbinfo =
		 * dbInfoMapper.selectByPrimaryKey(projectInfo.getMasterDb()); List<String>
		 * tableNames = Lists.newArrayList(); try { tableNames = selectAllTables(dbinfo,
		 * projectInfo.getDriverClassName()); } catch (SQLException e) {
		 * e.printStackTrace(); return new Result(false, e.getMessage(), tableNames); }
		 */
		// return new Result(true, "", db_return_list);
		return null;
	}

	@RequestMapping("addall")
	@ResponseBody
	public String addAll(@RequestParam("key") String tablesMap) {
		List<ProjectTables> tables = JSON.parseArray(tablesMap, ProjectTables.class);
		List<String> tables_name = new ArrayList<String>();
		int bizId = 0;
		for (int i = 0; i < tables.size(); i++) {
			try {
				if (StringUtils.isNotBlank(tables.get(i).getTableName())) {
					tables_name.add(tables.get(i).getTableName());
				}
				bizId = tables.get(i).getBizId();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (tables_name.size() > 0) {
			ProjectTablesExample example = new ProjectTablesExample();
			example.or().andTableNameIn(tables_name).andBizIdEqualTo(bizId);
			projectTablesMapper.deleteByExample(example);
		}
		for (int i = 0; i < tables.size(); i++) {
			try {
				
				ProjectBiz projectBiz = projectBizServiceImpl.selectProjectBizFillDb(bizId);
				ProjectInfo projectInfo = projectInfoMapper.selectByPrimaryKey(projectBiz.getProjectId());

				DbInfo dbinfo = projectBiz.getMaster();
				JDBC jdbc = new JDBC(projectInfo.getDriverClassName(), dbinfo.getJdbcUrl(), dbinfo.getUsername(),
						dbinfo.getPassword());
				String dbName = TableUtils.resoleSchema(dbinfo.getJdbcUrl());
				Connection conn = null;
				Statement stmt = null;
				ResultSet rs = null;
				String sql = "select a.COLUMN_NAME, a.COLUMN_COMMENT,a.COLUMN_KEY  from Information_schema.columns as a  where  a.table_Name = '"
						+ tables.get(i).getTableName() + "' and a.table_schema='" + dbName + "' and a.COLUMN_KEY='PRI'";
				try {
					conn = jdbc.getConnection();
					stmt = conn.createStatement();
					rs = stmt.executeQuery(sql);
					HtmlOperation ho = new HtmlOperation();
					try {
						while (rs.next()) {
							ProjectTables pj=tables.get(i);
							ho = new HtmlOperation();
							String pkName = rs.getString(1);
							tables.get(i).setPrimaryKey(pkName);
							tables.get(i).setTableOperation(JSON.toJSONString(pj));
							break;
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					JdbcUtils.closeResultSet(rs);
					JdbcUtils.closeStatement(stmt);
					JdbcUtils.closeConnection(conn);
				}

				projectTablesMapper.insertSelective(tables.get(i));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return "操作成功";
	}

}
